<?php
/**
 * @desc access for Ms Database
 * @author HoangNV2
 */
class SysMsSql {
	public $conn = NULL;
	public $sql = '';
	public $recordset;
	public $result;
	public $dbselected;
	public $oid;	
	
	public function __construct() {
		
	}
	
	/**
	 * use mssql_connect()
	 * @param $Server
	 * @param $User
	 * @param $Pass
	 * @param $Dbname
	 */
	function connect () {
		$Server = $this->config['host'];
		$User = $this->config['username'];
		$Pass = $this->config['password'];
		$DB = $this->config['dbname'];
		$port = $this->config['port'];
		
		if (!function_exists('mssql_pconnect')) $this->exceptionError('Do not support database mssql');
		
		//connection to the database
		$this->conn = @mssql_pconnect($Server, $User, $Pass) OR $this->exceptionError(mssql_get_last_message());
		
		//select a database to work with
		$dbselected = @mssql_select_db($DB, $this->conn) OR $this->exceptionError(mssql_get_last_message());
	}
	
	function getConnetion() {
		if ($this->conn == NULL) $this->connect();
		return $this->conn;
	}
	
	public function query($sql='') {
		$this->sql = $sql;
		if ($this->conn == NULL) $this->connect();
		$this->recordset = mssql_query($sql) OR $this->exceptionError(mssql_get_last_message());
		return $this->recordset;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function select ($sql='',$key='',$page_record=NULL,$page_no=NULL) {
		$this->query($sql);
		if ($key == '') {
			while ($row = mssql_fetch_assoc($this->recordset)) {
				$this->result[] = $row;
			}
		} else {
			$this->result = array();
			while ($row = mssql_fetch_assoc($this->recordset)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');
				
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}

	/**
	 * @param string $sql SQL QUERY
	 * @param string $key key of return result
	 * @param string $page_record number row of page
	 * @param string $page_no page number
	 * @return rows: array(
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 		key=>array(row1, row2,...),
	 * 	)
	 */
	public function getSelect ($res=NULL,$key='') {
		if (!$res) return ;

		if ($key == '') {
			while ($row = mssql_fetch_assoc($res)) {
				$this->result[] = $row;
			}
		} else {
			$this->result = array();
			while ($row = mssql_fetch_assoc($res)) {
				if (!isset($row[$key])) $this->exceptionError('Do not exist key ['.$key.']');
				$this->result[$row[$key]][] = $row;
			}
		}
		return $this->result;
	}
	
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function getSelectOne ($res=NULL) {
		if (!$res) return ;
		
		$this->result = mssql_fetch_assoc($res);
		return $this->result;
	}
	
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function fetchAll ($sql) {
		$this->query($sql);
		while ($row = mssql_fetch_assoc($this->recordset)){
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function fetch ($sql) {
		$this->query($sql);
		$this->result = array();
		while ($row = mssql_fetch_assoc($this->recordset)) {
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(col1,col2,...), 1=>array2(col1, col2,...),...)
	 */
	public function getFetchAll ($res=NULL) {
		if (!$res) return ;

		while ($row = mssql_fetch_assoc($res)){
			$this->result[] = $row;
		}
		return $this->result;
	}
	
	/**
	 * @param string $sql SQL QUERY
	 * @return rows: array(0=>array1(0=>val1,1=>val2,...), 1=>array2(0=>val1,1=>val2,...),...)
	 */
	public function getFetch ($res=NULL) {
		if (!$res) return ;
		$this->result = array();
		while ($row = mssql_fetch_assoc($res)) {
			$this->result[] = $row;
		}
		return $this->result;
		
	}
	
	/**
	 * @desc use INSERT,DELETE,UPDATE
	 * @return:
	 * 		- ROLLBACK if false
	 * 		- COMMIT if true
	 */
	public function queryRollback ($sql='') {
		$this->sql = $sql;
		if ($this->database == NULL) $this->connect();

		mssql_query("START TRANSACTION");
		$res = @mssql_query($sql);

		// Verify and end the transaction as appropriate.
		if (!$res) {
			mssql_query("ROLLBACK");
			$this->exceptionError(mssql_get_last_message());
		} else {
			$oid = mysql_insert_id();
			if (!is_null($oid)) $this->oid = array('id'=>$oid);
			mssql_query("COMMIT");
		}
		$this->recordset = $res;
		return $this->recordset;
	}

	/**
	 * @desc insert one record
	 * @return array new record id OR false
	 */
	public function insert ($sql='', $key_name='') {
		if ($key_name != '') {
			$sql.= "\n RETURNING {$key_name}";
		}
		
		$res = $this->queryRollback($sql);
		$oid = mssql_fetch_assoc($res);
		if (!is_null($oid)) {
			$this->oid = $oid;
			return $this->oid;
		}
		
		if ($this->recordset) return true;
		return false;
	}
	
	/**
	 * @return true|false
	 */
	public function update ($sql,$limit='') {
		$this->queryRollback($sql);
		if ($this->recordset) return true;
		
		return false;
	}
	
	public function delete ($sql, $limit='') {
		$this->queryRollback($sql);
		if ($this->recordset) return true;
		
		return false;
	}
	
	/**
	 * @desc get one first of rows
	 * @return array record
	 */
	public function selectOne ($sql) {
		$sql.= "\n LIMIT 1";
		$this->query($sql);
		$this->result = mssql_fetch_assoc($this->recordset);
		return $this->result;
	}
	
	/**
	 * @return nums row
	 */
	public function getNumRows () {
		if ($this->recordset == '') return 0;
		return mssql_num_rows($this->recordset);
	}

	/**
	 * @return print error
	 */
	public function exceptionError ($msg, $act='') {
		if ($act == 'ROLLBACK') {
			 @mssql_query("ROLLBACK");
		}
		throw new SysExceptionModel($msg);
	}
	
	/**
	 * @return last insert id
	 */
	public function getLastId () {
		return $this->oid;
	}
	
	/**
	 * @desc close connection
	 */
	public function __destruct () {
		mssql_close($this->conn); 
	}
	
}